import pandas as pd
import numpy as np
# 1.读取数据，查看数据的行和列
df = pd.read_csv('air_data.csv')
# print(df.head())
# 找出 SUM_YR_1 SUM_YR_2 不为空 不为零的数值 SEG_KM_SUM 和 avg_discount 等于0的数值对应的数据
df = df[df['SUM_YR_1'].notnull() & df['SUM_YR_2'].notnull()]

df = df[(df['SUM_YR_1']!=0) & (df['SUM_YR_2']!=0)]
# print(df.shape)
df = df[(df['SEG_KM_SUM']!=0) & (df['avg_discount']!=0)]
print(df.shape)
# 查看处理后剩余的数据量
#
#
# 获取相关特征[[ "FFP_DATE", "LOAD_TIME", "FLIGHT_COUNT", "SUM_YR_1", "SUM_YR_2", "SEG_KM_SUM", "AVG_INTERVAL" , "MAX_INTERVAL", "avg_discount"]]
df = df[[ "FFP_DATE", "LOAD_TIME", "FLIGHT_COUNT", "SUM_YR_1",
          "SUM_YR_2", "SEG_KM_SUM", "AVG_INTERVAL" , "MAX_INTERVAL", "avg_discount"]]
# 使用LOAD_TIME 和 FFP_DATE计算，得到入会时间
df['FFP_DATE'] = pd.to_datetime(df['FFP_DATE']) # 入会日期    日期类型
df["LOAD_TIME"] = pd.to_datetime(df["LOAD_TIME"])# 离会日期
df['入会时间'] = df['FFP_DATE'] - df["LOAD_TIME"] # 日期形式
df['入会时间'] = df['入会时间'].astype(np.int64)/(60*60*24*10**9)
print(df)

# 使用SUM_YR_1 SUM_YR_2 SEG_KM_SUM 得到平均公里票价
df['平均公里票价'] = (df['SUM_YR_1'] + df['SUM_YR_2']) / df['SEG_KM_SUM']

# 保留"入会时间", "飞行次数", "平均每公里票价", "总里程", "时间间隔差值", "平均折扣率" 特征
df1 = df[['入会时间', 'FLIGHT_COUNT', '平均公里票价', 'SEG_KM_SUM', 'AVG_INTERVAL', 'avg_discount']]
print(df1)
# 最后计算最优K值    肘部法，轮廓系数
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score
import matplotlib.pyplot as plt

k = [2,3,4,5,6]
sse = [] # 肘部法
si = [] # 轮廓系数
for i in k:
    model = KMeans(i)
    model.fit(df1)
    label = model.predict(df1)
    sse.append(model.inertia_)
    si.append(silhouette_score(df1, label))

plt.plot(k, sse) # 肘部法
plt.show()
plt.plot(k, si) # 轮廓系数
plt.show()